import org.anyline.entity.Aggregation;
import org.anyline.entity.DataRow;
import org.anyline.entity.DataSet;
import org.anyline.handler.Uploader;
import org.anyline.office.docx.entity.WDocument;
import org.anyline.office.docx.entity.Wtable;
import org.anyline.office.docx.entity.Wtc;
import org.anyline.office.docx.entity.Wtr;
import org.anyline.simple.office.WordApplication;
import org.anyline.util.*;
import org.anyline.util.regular.RegularUtil;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;

import java.io.File;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.*;

@SpringBootTest(classes = WordApplication.class)
public class WordTest {

    @Test
    public void read() {
        File input = new File("D:\\w0821\\海水浴场导入文件.docx");
        File template1 = new File("D:\\w0821\\海水浴场统计概况导出文件.docx");
        File template2 = new File("D:\\w0821\\海水浴场评价结果导出文件.docx");
        File dir = new File("D:\\w0821");
        read(input, dir, template1, template2);
    }

    public void read(File input, File dir, File template1, File template2) {
        ConfigTable.LOG_QUERY_RESULT_ROWS = -1; //输入所有日志
        WDocument doc = new WDocument(input);
        List<Wtable> tables= doc.tables(); //全部table
        String txt = doc.getTexts();
        String year = RegularUtil.cut(txt, "表2-1", "年");
        List<String> positions = RegularUtil.cuts(txt, "表2", "年", "海水浴场监测数据统计表");
        DataSet set = new DataSet(); //数据集合
        String ymd = ""; //当前日期
        int size = tables.size();
        for(int i=0; i<size; i++){
            String position = positions.get(i);
            Wtable table = tables.get(i);
            List<Wtr> trs = table.getTrs();
            for(Wtr tr:trs){
                txt = tr.getTexts();
                if(txt.contains("断面")){
                    //标题行
                    continue;
                }
                List<Wtc> tcs = tr.getTcs();
                String cell_ymd = tcs.get(0).getTexts();
                if(cell_ymd.contains("水质常规监测项目")){
                    //切换新日期
                    ymd = year + "-" + cell_ymd.replace("水质常规监测项目","").replace(".", "-").trim();
                }
                String type_txt = tcs.get(1).getTexts();
                String type = type(type_txt);
                for(int col=2; col<=5; col++){
                    BigDecimal value = BasicUtil.parseDecimal(tcs.get(col).getTexts(), null);
                    if(null != value){
                        /*if(type_txt.contains("100m")){
                        //导出单位也是 100mL不用*10 了
                            value = value.multiply(BigDecimal.TEN);
                        }*/
                        DataRow row = set.add();
                        row.put("TYPE_CODE", type);
                        row.put("YMD", ymd);
                        row.put("SCOPE", col-1); //1:齐膝	2:齐腰	3:齐胸	4:浴场 (没用到)
                        row.put("VALUE", value);
                        row.put("LVL", lvl(type, value));
                        row.put("POSITION", position);
                        //row.put("TYPE_NAME", type_txt);
                    }
                }
            }
        }
        System.out.println(LogUtil.table(set));
        File copy = new File(dir, "海水浴场统计概况导出文件_" + System.currentTimeMillis() + ".docx");
        FileUtil.copy(template1, copy);
        WDocument export1 = new WDocument(copy);
        tables = export1.tables("主要超标项目及其超标倍数");  //根据内容定位
        txt = export1.getTexts();
        positions = RegularUtil.cuts(txt, "表4.7", "年", "海水浴场");
        size = tables.size();
        for(int i=0; i<size; i++){
            Wtable table = tables.get(i);
            String position = positions.get(i);
            //测量范围(最大最小值) 1:粪大肠菌群  2:肠球菌 3:透明度 	4:溶解氧 5:石油类（mg/L）	水质  类别	海水评价类别	游泳适宜度
            //当前浴场条目
            DataSet position_rows =  set.getRows("POSITION", position);
            for(String key:types.keySet()){
                String type = types.get(key)+"";
                //当前类别条件
                DataSet type_rows = position_rows.getRows("TYPE_CODE", type);
                BigDecimal min = type_rows.minDecimal("VALUE");      //最小值
                BigDecimal max = type_rows.maxDecimal("VALUE");      //最大值
                BigDecimal avg = type_rows.avg("VALUE");             //平均值
                int lvl12_qty = type_rows.select.lessEqual("LVL", 2).size();  //1类2类数量
                double rate = lvl12_qty*100.0/type_rows.size();
                String min_max = min + "~" + max;
                System.out.println(position+":"+key+":"+min_max + ", qty:"+ type_rows.size() + " avg:"+ NumberUtil.format(avg,formats.get(key))+" 达标:"+NumberUtil.format(rate, "0.00"));
                //测量范围
                table.setText(1, cols.get(key), min_max);
                //样品数（个）
                table.setText(2, cols.get(key), type_rows.size()+"");
                //达标率（%）
                table.setText(3, cols.get(key), NumberUtil.format(rate, "0.00"));
                //年均值
                table.setText(4, cols.get(key), NumberUtil.format(avg,formats.get(key)));

            }

            DataRow lvl = null;
            if(!position_rows.getRows("LVL", "3").isEmpty()){
                lvl = lvls.get("3");
            }else if(!position_rows.getRows("LVL", "2").isEmpty()){
                lvl = lvls.get("2");
            }else{
                lvl = lvls.get("1");
            }

            table.setText(1, 6, lvl.getString("NAME"));
            table.setText(1, 7, lvl.getString("WATER"));
            table.setText(1, 8, lvl.getString("SWIM"));

        }
        export1.save();

        copy = new File(dir, "海水浴场评价结果导出文件_" + System.currentTimeMillis() + ".docx");
        FileUtil.copy(template2, copy);
        WDocument export2 = new WDocument(copy);
        tables = export2.tables();
        txt = export2.getTexts();
        positions = RegularUtil.cuts(txt, "表3", "年", "海水浴场");
        size = tables.size();
        for(int i=0; i<size; i++) {
            Wtable table = tables.get(i);
            String position = positions.get(i);
            //测量范围(最大最小值) 1:粪大肠菌群  2:肠球菌 3:透明度 	4:溶解氧 5:石油类（mg/L）	水质  类别	海水评价类别	游泳适宜度
            //当前浴场条目
            DataSet position_rows = set.getRows("POSITION", position);
            //按日期分组 每组取最大LVL
            DataSet trs = position_rows.group("LVL", Aggregation.MAX, "YMD");
            //取第二行作模板
            Wtr template_tr = table.getTrs().get(1);
            for(DataRow tr:trs){
                tr.put("MD", RegularUtil.cut(tr.getString("YMD"), "-", RegularUtil.TAG_END).replace("-","."));
                DataRow max = tr.getRow("LVL_MAX");
                String lvl_code = max.getString("LVL");
                DataRow lvl = lvls.get(lvl_code);
                tr.copy(lvl, "NAME", "WATER", "SWIM");
                tr.put("TMP","——");
                table.insert(template_tr, tr, "MD", "WATER", "NAME", "SWIM", "TMP", "TMP");
            }
            table.remove(template_tr);
        }
        export2.save();
    }
    public static int lvl(String type, BigDecimal value){
        DataSet set = lvls_gt.get(type);
        if(null != set){
            DataSet tmps = set.select.lessEqual("MIN", value).select.greater("MAX", value);
            if(!tmps.isEmpty()){
                return tmps.getRow(0).getInt("LVL", 0);
            }
        }else{
            set = lvls_lt.get(type);
            DataSet tmps = set.select.less("MIN", value).select.greaterEqual("MAX", value);
            if(!tmps.isEmpty()){
                return tmps.getRow(0).getInt("LVL", 0);
            }
        }
        return 0;
    }
    private static LinkedHashMap<String, String> types = new LinkedHashMap<>();
    private static Map<String, Integer> cols = new HashMap<>();
    private static Map<String, String> formats = new HashMap<>();
    private static Map<String, DataSet> lvls_gt = new HashMap<>();
    private static Map<String, DataSet> lvls_lt = new HashMap<>();
    private static Map<String, DataRow> lvls = new HashMap<>();
    static {
        types.put("粪大肠菌群", "1");
        types.put("肠球菌", "2");
        types.put("透明度", "3");
        types.put("溶解氧", "4");
        types.put("石油类", "5");

        //导出位置
        cols.put("粪大肠菌群", 1);
        cols.put("肠球菌", 2);
        cols.put("透明度", 3);
        cols.put("溶解氧", 4);
        cols.put("石油类", 5);

        formats.put("粪大肠菌群", "0");
        formats.put("肠球菌","0.00");
        formats.put("透明度", "0.00");
        formats.put("溶解氧", "0.00");
        formats.put("石油类", "0.0000");

        DataSet set = new DataSet();
        //粪大肠菌群
        DataRow lvl1 = new DataRow();
        lvl1.put("LVL", 1);
        lvl1.put("MIN", 0);
        lvl1.put("MAX", 100);
        DataRow lvl2 = new DataRow();
        lvl2.put("LVL", 2);
        lvl2.put("MIN", 100);
        lvl2.put("MAX", 200);

        DataRow lvl3 = new DataRow();
        lvl3.put("LVL", 3);
        lvl3.put("MIN", 200);
        lvl3.put("MAX", Integer.MAX_VALUE);
        set.add(lvl1);
        set.add(lvl2);
        set.add(lvl3);
        lvls_gt.put("粪大肠菌群", set);
        lvls_gt.put("1", set);

        //肠球菌
        set = new DataSet();
        lvl1 = new DataRow();
        lvl1.put("LVL", 1);
        lvl1.put("MIN", 0);
        lvl1.put("MAX", 40);
        lvl2 = new DataRow();
        lvl2.put("LVL", 2);
        lvl2.put("MIN", 40);
        lvl2.put("MAX", 200);
        lvl3 = new DataRow();
        lvl3.put("LVL", 3);
        lvl3.put("MIN", 200);
        lvl3.put("MAX", Integer.MAX_VALUE);
        set.add(lvl1);
        set.add(lvl2);
        set.add(lvl3);
        lvls_gt.put("肠球菌", set);
        lvls_gt.put("2", set);


        //油类
        set = new DataSet();
        lvl2 = new DataRow();
        lvl2.put("LVL", 2);
        lvl2.put("MIN", 0);
        lvl2.put("MAX", 0.050);
        lvl3 = new DataRow();
        lvl3.put("LVL", 3);
        lvl3.put("MIN", 0.050);
        lvl3.put("MAX", Integer.MAX_VALUE);
        set.add(lvl2);
        set.add(lvl3);
        lvls_gt.put("油类", set);
        lvls_gt.put("5", set);


        //透明度/m	≥1.2	≥0.5.且<1.2	<0.5
        set = new DataSet();
        lvl1 = new DataRow();
        lvl1.put("LVL", 1);
        lvl1.put("MIN", 1.2);
        lvl1.put("MAX", Integer.MAX_VALUE);
        lvl2 = new DataRow();
        lvl2.put("LVL", 2);
        lvl2.put("MIN", 0.5);
        lvl2.put("MAX", 1.2);
        lvl3 = new DataRow();
        lvl3.put("LVL", 3);
        lvl3.put("MIN", 0);
        lvl3.put("MAX", 0.5);
        set.add(lvl1);
        set.add(lvl2);
        set.add(lvl3);
        lvls_lt.put("透明度", set);
        lvls_lt.put("3", set);

        //溶解氧/(mg · L)	≥6	≥5,且<6	<5
        set = new DataSet();
        lvl1 = new DataRow();
        lvl1.put("LVL", 1);
        lvl1.put("MIN", 6);
        lvl1.put("MAX", Integer.MAX_VALUE);
        lvl2 = new DataRow();
        lvl2.put("LVL", 2);
        lvl2.put("MIN", 6);
        lvl2.put("MAX", 6);
        lvl3 = new DataRow();
        lvl3.put("LVL", 3);
        lvl3.put("MIN", 0);
        lvl3.put("MAX", 5);
        set.add(lvl1);
        set.add(lvl2);
        set.add(lvl3);
        lvls_lt.put("溶解氧", set);
        lvls_lt.put("4", set);

        //质量等级	水质类别	游泳适宜度
        lvl1 = new DataRow();
        lvl1.put("NAME", "一类");
        lvl1.put("WATER", "优");
        lvl1.put("SWIM", "适宜游泳");
        lvls.put("1", lvl1);
        lvl2 = new DataRow();
        lvl2.put("NAME", "二类");
        lvl2.put("WATER", "良");
        lvl2.put("SWIM", "较适宜游泳");
        lvls.put("2", lvl2);
        lvl3 = new DataRow();
        lvl3.put("NAME", "三类");
        lvl3.put("WATER", "差");
        lvl3.put("SWIM", "不适宜游泳");
        lvls.put("3", lvl3);

    }

    public String type(String txt){
        for(String key:types.keySet()){
            if(txt.contains(key)){
                return types.get(key);
            }
        }
        return null;
    }
    @Test
    public void files() {
        WDocument top = doc();
        List<File> list = new ArrayList<>();
        list.add(new File("D:\\a1.docx"));
        list.add(new File("D:\\a2.docx"));
        top.replace("${PAGE}", list);
        top.save();
    }
    @Test
    public void file_page() {
        WDocument top = doc();
        List<File> list = new ArrayList<>();
        list.add(new File("D:\\c1.docx"));
        list.add(new File("D:\\c2.docx"));
        list.add(new File("D:\\c3.docx"));

        StringBuilder content = new StringBuilder();
        for(File word:list) {
            content.append("<word>").append(word.getAbsolutePath()).append("</word>").append("<div style=\"page-break-after: always;\">分页</div>");
        }

        top.replace("${PAGE}", content.toString());
        top.save();
    }
    @Test
    public void replace() {
        WDocument top = new WDocument(new File("D:\\c.docx"));
        top.replace("${sum}", "100");
        top.save();
    }
    @Test
    public void word2html() throws Exception{
        WDocument top = doc();
        //整个文档转html
       System.out.println(top.html());
        Wtable table = top.tables().get(0);

        //单个table转html
        System.out.println(table.html());

        //如果其中有图片可以提供一个Uploader,否则会生base64格式图片
        Uploader uploader = new Uploader() {
            @Override
            public String upload(String name, InputStream is) {
                //处理这个输入流并返回文件地址
                return "http://cdn.anyline.org/img/user/alvg.png";
            }
        };
        System.out.println(table.html(uploader));

    }
    private WDocument doc(){
        File file = new File("D:\\c.docx");
        File copy = new File("D:\\a_"+System.currentTimeMillis()+".docx");
        FileUtil.copy(file, copy);
        WDocument doc = new WDocument(copy);
        return doc;
    }
}
